package com.atguigu.flink.chapter05.sink;

import com.atguigu.flink.bean.WaterSensor;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * @Author lizhenchao@atguigu.cn
 * @Date 2022/1/13 11:02
 */
public class Flink03_Custom {
    public static void main(String[] args) {
        Configuration conf = new Configuration();
        conf.setInteger("rest.port", 20000);
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(conf);
        env.setParallelism(2);
        
        DataStream<WaterSensor> stream = env
            .socketTextStream("hadoop162", 9999)
            .map(line -> {
                String[] data = line.split(",");
                return new WaterSensor(data[0], Long.valueOf(data[1]), Integer.valueOf(data[2]));
            });
        // 自定义一个把数据写入到mysql的sink
        
        stream
            .keyBy(WaterSensor::getId)
            .sum("vc")
            .addSink(new MysqlSink());
        
        try {
            env.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    // 建立到mysql的连接, 应该把一个并行度一个连接
    public static class MysqlSink extends RichSinkFunction<WaterSensor> {
        
        private Connection conn;
        private PreparedStatement ps;
        
        @Override
        public void open(Configuration parameters) throws Exception {
            // 建立到mysql的连接
            String mysqlUrl = "jdbc:mysql://hadoop162:3306/test";
            //ctrl+alt+f
            conn = DriverManager.getConnection(mysqlUrl, "root", "aaaaaa");
            
            //            String sql = "insert into sensor(id, ts, vc) values(?,?,?)";
            // 幂等性
            //            String sql = "insert into sensor(id, ts, vc) values(?,?,?) on duplicate key update vc=?";
            String sql = "replace into sensor(id, ts, vc) values(?,?,?)";
            ps = conn.prepareStatement(sql);
        }
        
        @Override
        public void close() throws Exception {
            
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
            // 关闭连接
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
            
        }
        
        // 通过jdbc连接把每天数据写入到mysql中
        @Override
        public void invoke(WaterSensor value,
                           Context context) throws Exception {
            
            // 给sql中的占位符赋值
            ps.setString(1, value.getId());
            ps.setLong(2, value.getTs());
            ps.setInt(3, value.getVc());
            
//            ps.setInt(4, value.getVc());
            
            //执行sql
            ps.execute();
            
        }
    }
}
